Alias

Alias#

Aliases are like nicknames, a temporary name given to a table or a column to write expressive and readable queries. We can use aliases with columns, tables, and MySQL functions.

Example Syntax#

SELECT col1

AS aliasCol1

FROM table;

Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy and paste the command ./DataJek/Lessons/20lesson.sh and wait for the MySQL prompt to start-up.

1. We have the first name column in the Actors table. Since most actors are known by their first names, we can alias the FirstName column as PopularName in the following select query:

   SELECT FirstName AS PopularName from Actors;
   

Observe that the column heading in the output shows the alias PopularName instead of FirstName column.

2. We can also use aliases with MySQL functions. For instance, we use the concat function to print the full name for an actor as follows:

   SELECT CONCAT(FirstName,' ', SecondName) AS FullName FROM Actors;
   

So far so good, but you may question the utility of aliases as the effects may seem only cosmetic. Aliases become powerful when they are used in subqueries or other clauses as shown next.

3. We can now sort the actors by their full names which are displayed by the concat function as follows:

   SELECT CONCAT(FirstName,' ', SecondName) AS FullName FROM Actors ORDER BY FullName;
   

Note the sorted output. Without using the alias feature the same query would be written as follows:

SELECT CONCAT(FirstName,' ', SecondName) FROM Actors ORDER BY CONCAT(FirstName,' ', SecondName);

The above query is verbose compared to the one written using the alias.

4. Aliases can be used in GROUP BY, HAVING, and ORDER BY clauses. Notably, aliases for columns can’t be used in the WHERE clause but aliases for table can, as shown next.

   We can use aliases as shorthand for tables too. Table aliases come in handy when working with complex queries that involve joins, which we’ll cover later. For now, we’ll show a simple example of using an alias for the Actors table in the following select query:

   SELECT FirstName FROM Actors AS tbl WHERE tbl.FirstName='Brad' AND tbl.NetWorthInMillions > 200;
   

Note in the above query, we use tbl as the alias for the Actors table and then in the WHERE clause the shorthand is used to refer to the two column names of the Actors table.

5. We can also use the table alias in the SELECT clause before we actually define the alias. We rewrite the previous query and use the alias in the SELECT clause as follows:

   SELECT tbl.FirstName FROM Actors AS tbl WHERE tbl.FirstName='Brad' AND tbl.NetWorthInMillions > 200;
   

6. For some queries table aliases are inevitable. For instance, we can alias the Actors table twice to find out all the actors with the same net worth in a single query. Think of picking each row and comparing it with the rest of the rows in the table to find two rows with the same NetWorthInMillions column. However, the caveat is that we want to skip the row when it tries to match with itself. The complete query is presented below:

   SELECT t1.FirstName, t1.NetworthInMillions
   FROM Actors AS t1,
   Actors AS t2
   WHERE t1.NetworthInMillions = t2.NetworthInMillions
   AND t1.Id != t2.Id;